package li.nibble.twittertido.model;

import java.sql.Timestamp;
import java.util.ArrayList;

import li.nibble.twittertido.Utils;
import li.nibble.twittertido.beans.Tweet;
import li.nibble.twittertido.webservice.Client;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public abstract class DataBase {

	public static final String TABLE_NAME = "preferences";
	public static final String TABLE_HOME = "home";
	public static final String TABLE_QUEUE = "queue";
	public static final String PREFERENCES_NAME = "name";
	public static final String PREFERENCES_VALUE = "value";
	public static final String PREFERENCES_NAME_USERNAME = "username";
	public static final String PREFERENCES_NAME_USER_ID = "user_id";
	public static final String PREFERENCES_NAME_EMAIL = "email";
	public static final String PREFERENCES_NAME_PASSWORD = "password";
	public static final String PREFERENCES_NAME_LAST_HOME = "last_home";
	public static final String PREFERENCES_NAME_LAST_QUEUE = "last_queue";

	public static final String TWEET_ID = "id";
	public static final String TWEET = "tweet";
	public static final String TWEET_USER = "user";
	public static final String TWEET_DATE = "date";
	public static final String TWEET_LIKE = "like";

	public static boolean registered(Context context) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();

		Cursor data_cursor = db.query(TABLE_NAME,
				new String[] { PREFERENCES_VALUE }, PREFERENCES_NAME + " = '"
						+ PREFERENCES_NAME_USERNAME + "'", null, null, null,
				null);

		if (data_cursor.getCount() > 0) {
			db.close();
			return true;
		}

		db.close();
		return false;
	}

	public static void localLogin(Context context, String username,
			String password, int id, String email) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put("name", PREFERENCES_NAME_USERNAME);
		values.put("value", "");

		// devuelve -1 si no se pudo insertar
		@SuppressWarnings("unused")
		long data_cursor = db.insert(TABLE_NAME, null, values);

		values.put("name", PREFERENCES_NAME_PASSWORD);
		values.put("value", password);

		// devuelve -1 si no se pudo insertar
		data_cursor = db.insert(TABLE_NAME, null, values);

		values.put("name", PREFERENCES_NAME_EMAIL);
		values.put("value", email);

		// devuelve -1 si no se pudo insertar
		data_cursor = db.insert(TABLE_NAME, null, values);

		values.put("name", PREFERENCES_NAME_USER_ID);
		values.put("value", id);

		// devuelve -1 si no se pudo insertar
		data_cursor = db.insert(TABLE_NAME, null, values);

		values.put("name", PREFERENCES_NAME_LAST_QUEUE);
		values.put("value", "2010-01-01 00:00:00");

		// devuelve -1 si no se pudo insertar
		data_cursor = db.insert(TABLE_NAME, null, values);

		values.put("name", PREFERENCES_NAME_LAST_HOME);
		values.put("value", "2010-01-01 00:00:00");

		// devuelve -1 si no se pudo insertar
		data_cursor = db.insert(TABLE_NAME, null, values);

		insertLocalTweets(context, Client.getNewHome(context), TABLE_HOME);
		insertLocalTweets(context, Client.getNewQueue(context), TABLE_QUEUE);

		db.close();
	}
	
	public static void setPreference(Context context, String key, String value){
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("name", key);
		values.put("value", value);

		// devuelve -1 si no se pudo insertar
		long data_cursor = db.insert(TABLE_NAME, null, values);
		if(data_cursor < 0){
			db.update(TABLE_NAME, values, "name = ?", new String[]{key});
		}
		db.close();
	}

	public static Tweet getTweet(Context context, int id) {
		Tweet tweet = new Tweet();
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();

		Cursor data_cursor = db.query(TABLE_HOME, new String[] { TWEET_ID,
				TWEET_USER, TWEET, TWEET_DATE, TWEET_LIKE }, TWEET_ID + "='" + id + "'",
				null, null, null, null);
		if (data_cursor.moveToFirst()) {
			tweet.setId(data_cursor.getInt(0));
			tweet.setUser(data_cursor.getString(1));
			tweet.setTweet(data_cursor.getString(2));
			tweet.setDate(data_cursor.getString(3));
			tweet.setLikes(data_cursor.getInt(4));
		} else {
			db.close();
			return null;
		}

		db.close();
		
		
		
		return tweet;
	}

	public static boolean removeTweet(Context context, Tweet tweet, String table) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();

		db.delete(table, TWEET_ID + "=" + tweet.getId(), null);

		db.close();
		return true;
	}

	public static boolean insertLocalTweets(Context context,
			ArrayList<Tweet> tweets, String table) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();

		for (Tweet tweet : tweets) {
			String last = PREFERENCES_NAME_LAST_HOME;
			if (table.compareToIgnoreCase(TABLE_QUEUE) == 0) {
				last = PREFERENCES_NAME_LAST_QUEUE;
			}

			Timestamp last_home = Utils.convertStringToTimestamp(getPreference(
					context, last));
			ContentValues values = new ContentValues();
			values.put(TWEET_ID, tweet.getId());
			values.put(TWEET_USER, tweet.getUser());
			values.put(TWEET, tweet.getTweet());
			values.put(TWEET_DATE, tweet.getDate());
			if (table.equalsIgnoreCase(TABLE_HOME)) {
				values.put(TWEET_LIKE, 0);
			}
			db.insert(table, null, values);

			if (table.equalsIgnoreCase(TABLE_HOME)) {
				removeTweet(context, tweet, TABLE_QUEUE);
			}

			Timestamp tweet_date = Utils.convertStringToTimestamp(tweet
					.getDate());
			
//			Log.e("DATE", tweet_date+" - "+last_home);
			
			if (tweet_date.compareTo(last_home) > 0) {
				ContentValues values_last_home = new ContentValues();
				values_last_home.put("value", tweet.getDate());
				db.update(TABLE_NAME, values_last_home, PREFERENCES_NAME
						+ "= '" + last + "'", null);
			}

			// Log.e("DataBase.insertTweet ("+table+")", result + "");
		}

		db.close();
		return true;
	}

	public static String getPreference(Context context, String name) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor data_cursor = db.query(TABLE_NAME,
				new String[] { PREFERENCES_VALUE }, PREFERENCES_NAME + " = '"
						+ name + "'", null, null, null, null);
		if (data_cursor.moveToFirst()) {
			String result = data_cursor.getString(0);
			db.close();
			return result;
		} else {
			db.close();
			return null;
		}
	}

	public static ArrayList<Tweet> getLocalTweet(Context context, String table) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();

		ArrayList<Tweet> tweets = new ArrayList<Tweet>();

		Cursor data_cursor = db.query(table, new String[] { TWEET_ID,
				TWEET_USER, TWEET, TWEET_DATE }, null, null, null, null,
				"RANDOM()");

		if (table.equalsIgnoreCase(TABLE_HOME)) {
			data_cursor = db.query(table, new String[] { TWEET_ID, TWEET_USER,
					TWEET, TWEET_DATE, TWEET_LIKE }, null, null, null, null,
					"date DESC");
		}

		while (data_cursor.moveToNext()) {
			Tweet tweet = new Tweet();
			tweet.setId(data_cursor.getInt(0));
			tweet.setUser(data_cursor.getString(1));
			tweet.setTweet(data_cursor.getString(2));
			tweet.setDate(data_cursor.getString(3));
			if (table.equalsIgnoreCase(TABLE_HOME)) {
				tweet.setLikes(data_cursor.getInt(4));
			}
			tweets.add(tweet);
		}

		db.close();

		return tweets;
	}

//	public static Tweet getLocalTweetHome(Context context, int id) {
//		Helper helper = new Helper(context);
//		SQLiteDatabase db = helper.getWritableDatabase();
//		Cursor data_cursor = db.query(TABLE_HOME, new String[] { TWEET_ID,
//				TWEET_USER, TWEET, TWEET_DATE, TWEET_LIKE }, "id = '" + id
//				+ "'", null, null, null, "date DESC");
//		Tweet tweet = new Tweet();
//		if (data_cursor.moveToFirst()) {
//			tweet.setId(data_cursor.getInt(0));
//			tweet.setUser(data_cursor.getString(1));
//			tweet.setTweet(data_cursor.getString(2));
//			tweet.setDate(data_cursor.getString(3));
//			tweet.setLikes(data_cursor.getInt(4));
//		}
//		db.close();
//		return tweet;
//	}

	public static void printTweet(Context context, String table) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor data_cursor = db.query(table, new String[] { TWEET_ID,
				TWEET_USER, TWEET, TWEET_DATE }, null, null, null, null, null);

		while (data_cursor.moveToNext()) {
			Log.e("DataBase.printTweetHome", data_cursor.getString(0) + " - "
					+ data_cursor.getString(1));
		}

		db.close();
	}

	public static void printPrefences(Context context) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();
		Cursor data_cursor = db.query(TABLE_NAME, new String[] {
				PREFERENCES_NAME, PREFERENCES_VALUE }, null, null, null, null,
				null);

		while (data_cursor.moveToNext()) {
			Log.e("DataBase.printPreferences", data_cursor.getString(0) + " - "
					+ data_cursor.getString(1));
		}

		db.close();
	}

	public static void updateLikeTweet(Context context, int tweet, int like) {
		Helper helper = new Helper(context);
		SQLiteDatabase db = helper.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(TWEET_LIKE, like);
		db.update(TABLE_HOME, values, TWEET_ID + "= '" + tweet + "'", null);
		db.close();
	}

}
